package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.vo.OrderVo;

public class OrderDao {
	// 页大小
		private int pageSize = 7;
	
	/*
	 *获取一个学员的订单信息 
	 */
	public OrderVo getStuById (String sid) throws SQLException{
		String sql = "SELECT o.*,c.cust_name,ss.status_name, c.* from `order` o"
				 +" INNER JOIN customer c on  o.order_cust_name_id = c.cust_id"
				 +" INNER JOIN `status` ss on c.cust_status_id = ss.status_id"
				 +" where c.cust_status_id = '2' or c.cust_status_id = '3' and o.order_id = ?" ;
		Map<String,Object> data = Jdbc.getInstace().executeQuery(sql, sid);		
		OrderVo orderVo = null;
		if(data != null){
			orderVo = buildOrderVo(data);			
		}
		return orderVo;
	}
	
	//获取当前页的学员订单信息
	public List<OrderVo> getOrderAllList(int status,int page,String s) throws SQLException{
		int start = (page - 1) * pageSize;
		String sql = "SELECT o.*,c.cust_name,ss.status_name,c.* from `order` o"
				 +" INNER JOIN customer c on  o.order_cust_name_id = c.cust_id"
				 +" INNER JOIN `status` ss on c.cust_status_id = ss.status_id"
				 +" where c.cust_status_id = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, status,sql, s);
		sql += " order by o.order_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		List<OrderVo> aa=queryOrderList(sql, params.toArray());
		return aa;		
	}
	
	//获取总页数
	public int getTotalPage(int type,String s) throws SQLException{
		String sql = "select count(cust_name) total	from `order` INNER JOIN customer on `order`.order_cust_name_id=customer.cust_id  ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, s);
		Map<String,Object> map = Jdbc.getInstace().executeQuery(sql,params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}
	
	private List<OrderVo> queryOrderList(String sql,Object ...params ) throws SQLException{
		List<Map<String,Object>> dataList = Jdbc.getInstace().executeQueryForList(sql, params);
		List<OrderVo> orderList = null; 
		if(dataList != null && dataList.size() > 0){
			orderList = new ArrayList<>();
			for (Map<String,Object> data : dataList) {
				orderList.add(buildOrderVo(data));
			}
		}
		return orderList;
	}
	private OrderVo buildOrderVo(Map<String, Object> data){
		OrderVo orderVo = new OrderVo();
		orderVo.setOrderId(Integer.parseInt(data.get("order_id").toString()));
		orderVo.setOrderCustName(data.get("cust_name").toString());
		orderVo.setOrderCustNameId(Integer.parseInt(data.get("order_cust_name_id").toString()));
		orderVo.setCustomStatusId(Integer.parseInt(data.get("cust_status_id").toString()));
		orderVo.setCustomStatus(data.get("status_name").toString());
		return orderVo;
	}
	private String buildSqlAndParams(List<Object> params, int type,String sql, String s) {
		params.add(type);
		//params.add(sta);
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " AND  c.cust_name LIKE ? ";
			params.add("%" + s + "%");
		}
		return sql;
	}
	private String buildSqlAndParams(List<Object> params, String sql, String s) {
		if (!StringUtils.isNullOrEmpty(s)) {
			sql += " AND cust_name LIKE ? ";
			params.add("%" + s + "%");
		}
		return sql;
	}
}
